#pragma once
#include <jsoncpp/json/json.h>
#include <mysql/mysql.h>
#include <iostream>
#include <string>
#include <mutex>
#include <cstdlib>

#define HOST "127.0.0.1"
#define USER "root"
#define DB_Name "vod_system"

// 数据库管理模块
namespace vod
{
    //--------------------------------------------  Mysql Api --------------------------------------------------
    
    static MYSQL *MysqlInit()
    {
        MYSQL *mysql = mysql_init(nullptr); // 1.初始化mysql句柄 -- 默认动态申请
        if (mysql == nullptr)
        {
            std::cout << "mysql_init failed!!!" << std::endl;
            return nullptr;
        }

        // 2.连接mysqld == mysql -u root -p ""
        if (mysql_real_connect(mysql, HOST, USER, nullptr, DB_Name, 3306, nullptr, 0) == nullptr)
        {
            std::cout << "mysql_real_connect failed!!!" << std::endl;
            mysql_close(mysql);
            return nullptr;
        }

        if (mysql_set_character_set(mysql, "utf8") != 0) // 3.设置Client字符编码集 == charset=utf8;
        {
            std::cout << "mysql_set_character_set failed!!!" << std::endl;
            mysql_close(mysql);
            return nullptr;
        }

        if (mysql_select_db(mysql, DB_Name)) // 4.选择操作的数据库 == use database;
        {
            std::cout << "mysql_select_db failed!!!" << std::endl;
            return nullptr;
        }
        return mysql;
    }

    static void DestoryMysql(MYSQL *mysql)
    {
        if (mysql != nullptr)
            mysql_close(mysql);
    }

    static bool MysqlQuery(MYSQL *mysql, const std::string &Instructions)
    {
        if (mysql_query(mysql, Instructions.c_str()) != 0)
        {
            std::cout << Instructions << " <--> " << mysql_error(mysql) << std::endl;
            return false;
        }
        return true;
    }

    //--------------------------------------------  Table Video --------------------------------------------------

    class TableVideo
    {
    public:
        TableVideo();
        ~TableVideo();
        bool Insert(const Json::Value &VideoData);                       // 插入新的视频信息到数据库表中
        bool Update(size_t video_id, Json::Value &VideoData);            // 修改数据库中表的视频信息
        bool Delete(size_t video_id);                                    // 删除数据库中表的视频信息
        bool SelectAll(Json::Value *VideoDatas);                         // 获取表中全部视频信息
        bool SelectOne(size_t video_id, Json::Value *VideoData);         // 根据视频id获取表中的单个视频信息
        bool SelectLike(const std::string &key, Json::Value *VideoData); // 模糊匹配 -- 获取与key相关的视频信息

    private:
        MYSQL *mysql_;
        std::mutex mtx_;
    };

    //-------------------------------------------- Table add del mod check API--------------------------------------------------

    TableVideo::TableVideo()
    {
        mysql_ = MysqlInit();
        if (mysql_ == nullptr)
            exit(1);
    }

    TableVideo::~TableVideo()
    {
        DestoryMysql(mysql_);
    }

    bool TableVideo::Insert(const Json::Value &VideoData)
    {
        if (VideoData["name"].asString().size() == 0)
        {
            std::cout << "The length of the name field in the inserted table is 0" << std::endl;
            return false;
        }

        if (VideoData["info"].asString().size() == 0)
        {
            std::cout << "The length of the info field in the inserted table is 0" << std::endl;
            return false;
        }

        // table data: id、name、info、video、image
        std::string newTableData;
        newTableData.resize(2048 + VideoData["info"].asString().size()); // 防止视频描述(info)长度过大
// newTableData: insert into tb_video values(id,'name','info','video,image');
#define INSET_DATA "insert into tb_video values (NULL, '%s', '%s', '%s', '%s');"
        sprintf(&(newTableData[0]), INSET_DATA, VideoData["name"].asCString(), VideoData["info"].asCString(),
                VideoData["video"].asCString(), VideoData["image"].asCString());

        return MysqlQuery(mysql_, newTableData);
    }

    bool TableVideo::Update(size_t video_id, Json::Value &VideoData)
    {
        if (VideoData["name"].asString().size() == 0)
        {
            std::cout << "The length of the name field in the inserted table is 0" << std::endl;
            return false;
        }

        if (VideoData["info"].asString().size() == 0)
        {
            std::cout << "The length of the info field in the inserted table is 0" << std::endl;
            return false;
        }

        std::string sql;
        sql.resize(2048 + VideoData["info"].asString().size());

// sql: update tb_video set name=???, info=??? where id = ??;
#define UPDATA_VIDEO "update tb_video set name='%s', info='%s' where id=%d;"
        sprintf(&sql[0], UPDATA_VIDEO, VideoData["name"].asCString(),
                VideoData["info"].asCString(), video_id);

        return MysqlQuery(mysql_, sql);
    }

    bool TableVideo::Delete(size_t video_id)
    {
#define DELETE_VIDEO "delete from tb_video where id=%u;"
        char sql[128];
        sprintf(sql, DELETE_VIDEO, video_id);

        return MysqlQuery(mysql_, sql);
    }

    //-------------------------------------------- Table Data Search API--------------------------------------------------

    bool TableVideo::SelectAll(Json::Value *VideoDatas)
    {
#define SELECT_ALL "select * from tb_video;"
        mtx_.lock();                               // 保护查询结果和保存到本地的过程
        bool ret = MysqlQuery(mysql_, SELECT_ALL); // 1.执行sql语句
        if (ret == false)
        {
            mtx_.unlock();
            return false;
        }

        MYSQL_RES *mqr = mysql_store_result(mysql_); // 2.保存result to 本地
        if (mqr == nullptr)
        {
            std::cout << "mysql store result error: " << mysql_error(mysql_) << std::endl;
            mtx_.unlock();
            return false;
        }
        mtx_.unlock();

        // 3.获取result set 的行数
        size_t row = mysql_num_rows(mqr);

        // 4. 遍历结果集，将每行数据保存到VideoDatas
        for (int i = 0; i < row; ++i)
        {
            // 将结果集数据保存到二维数组中，row_data[0]为第一行数据
            MYSQL_ROW row_data = mysql_fetch_row(mqr);

            // desc tb_video: "id name info video image"
            Json::Value videodata;
            videodata["id"] = std::stoi(row_data[0]);
            videodata["name"] = row_data[1];
            videodata["info"] = row_data[2];
            videodata["video"] = row_data[3];
            videodata["image"] = row_data[4];
            VideoDatas->append(videodata);
        }
        // 5. 释放结果集(MYSQL_RES)
        mysql_free_result(mqr);
        return true;
    }

    bool TableVideo::SelectOne(size_t video_id, Json::Value *VideoData)
    {
#define SELECT_ONE "select * from tb_video where id=%u;"
        char sql[128];
        sprintf(sql, SELECT_ONE, video_id);

        mtx_.lock();
        bool ret = MysqlQuery(mysql_, sql);
        if (ret == false)
        {
            mtx_.unlock();
            return false;
        }

        MYSQL_RES *mqr = mysql_store_result(mysql_);
        if (mqr == nullptr)
        {
            std::cout << "mysql store result error: " << mysql_error(mysql_) << std::endl;
            mtx_.unlock();
            return false;
        }
        mtx_.unlock();

        size_t row = mysql_num_rows(mqr);
        // id是表中唯一主键属性，不存在冗余
        if (row != 1)
        {
            std::cout << "Table data not found" << std::endl;
            return false;
        }

        MYSQL_ROW row_data = mysql_fetch_row(mqr);
        (*VideoData)["id"] = std::stoi(row_data[0]);
        (*VideoData)["name"] = row_data[1];
        (*VideoData)["info"] = row_data[2];
        (*VideoData)["video"] = row_data[3];
        (*VideoData)["image"] = row_data[4];

        mysql_free_result(mqr);
        return true;
    }

    bool TableVideo::SelectLike(const std::string &key, Json::Value *VideoDatas)
    {
#define SELECT_LIKE "select * from tb_video where name like  '%%%s%%';" // "%%" == 一个%，%是一个转义字符
        char sql[128];
        sprintf(sql, SELECT_LIKE, key.c_str());

        mtx_.lock();
        bool ret = MysqlQuery(mysql_, sql);
        if (ret == false)
        {
            mtx_.unlock();
            return false;
        }

        MYSQL_RES *mqr = mysql_store_result(mysql_);
        if (mqr == nullptr)
        {
            std::cout << "mysql store result error: " << mysql_error(mysql_) << std::endl;
            mtx_.unlock();
            return false;
        }
        mtx_.unlock();

        size_t row = mysql_num_rows(mqr);
        for (int i = 0; i < row; ++i)
        {
            MYSQL_ROW row_data = mysql_fetch_row(mqr);
            Json::Value videodata;
            videodata["id"] = std::stoi(row_data[0]);
            videodata["name"] = row_data[1];
            videodata["info"] = row_data[2];
            videodata["video"] = row_data[3];
            videodata["image"] = row_data[4];
            VideoDatas->append(videodata);
        }
        mysql_free_result(mqr);
        return true;
    }
};